import json
import logging
#import pandas as pd
import os
from django.db import connection
from django.http import JsonResponse
from django.http import FileResponse
from django.shortcuts import render
from openpyxl import load_workbook
from wxcloudrun.models import Counters

logger = logging.getLogger('log')


def extfile1():
    #生成的文件在IOS手机上打不开 安卓手机上没有试
    cursor = connection.cursor()
    localfile = 'wxcloudrun/templates/checkinfonew.xlsx'
    tmp = 'wxcloudrun/templates/checkinfo.xlsx'
    wb = load_workbook(tmp, read_only=False)
    ws = wb['Sheet1']
    sql = 'select area_name as 小区名称,check_date as 宣传日期,user_name as 宣传人 from checkinfo order by check_date'

    cursor.execute(sql)
    rowvalue = cursor.fetchone()
    rowid = 1
    while rowvalue:
        rowid = rowid + 1
        i = 0
        for val1 in rowvalue:
            i = i + 1
            ws.cell(rowid, i).value = str(val1)
        rowvalue = cursor.fetchone()
    wb.save(localfile)
    wb.close()
    print(localfile + "生成完成")


def extfile(sql, filePath):
    # extfile("select area_name,check_date,user_name from checkinfo",'wxcloudrun/templates/checkinfo.xls')
    # 上传到云主机后，乱码问题没有解决 2022.3.10
    cursor = connection.cursor()
    f = open(filePath, "w", encoding='GBK')
    cursor.execute(sql)
    SqlDomain = cursor.description
    for i in range(len(SqlDomain)):
        f.write(SqlDomain[i][0])
        f.write('\t')
    f.write("\n")
    rowvalue = cursor.fetchone()
    rowid = 1
    while rowvalue:
        rowid = rowid + 1
        for val1 in rowvalue:
            f.write(str(val1))
            f.write('\t')
        f.write("\n")
        if (rowid % 1000 == 0):
            print(rowid)
        rowvalue = cursor.fetchone()
    f.close()


def index(request, _):
    """
    获取主页
     `` request `` 请求对象
     云托管环境 不能使用pandas

    sql = 'select area_name as 小区名称,check_date as 宣传日期,user_name as 宣传人 from checkinfo order by check_date'
    data=pd.read_sql(sql,connection)
    data.to_excel("checkinfotmp.xlsx", index=False)
    print(data)
    """
    #filepath = os.path.split(os.path.realpath(__file__))[0]
    #print(filepath)
    #extfile("select area_name,check_date,user_name from checkinfo",'wxcloudrun/templates/checkinfo.xls')
    extfile1()

    rsp = JsonResponse({'code': 0, 'errorMsg': ''},
                       json_dumps_params={'ensure_ascii': False})
    return download_xlsx("checkinfotmp.xlsx")


def download_template(request, filepath):
    file = open(filepath, 'rb')
    response = FileResponse(file)
    response['Content-Type'] = 'application/octet-stream'
    response['Content-Disposition'] = 'attachment;filename="tempTemplate.xls"'
    return response


def download_xlsx(filepath):
    file = open(filepath, 'rb')
    response = FileResponse(file)
    #response['Content-Type'] = 'application/octet-stream'
    response['Content-Type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=GBK'
    response['Content-Disposition'] = 'attachment;filename="tempTemplate.xlsx"'
    return response
